//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Copyright 2007 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   COUNTRY
// AUTHOR           :   Craig Chun
// DATE CREATE      :   2007/07/12
// DESCRIPTION      :   a COMMON DAO file for the function, COUNTRY
//                  :
//                  :
//                  :
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2007/07/12   Craig Chun      1.0                 source is generated.
//
//
//
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

package com.erp.app.setup.country.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.setup.country.dao.CountryCommonDao;
import com.erp.app.setup.country.vo.CountryHdrVo;
import com.erp.app.setup.country.bizLogic.CountryCommonUtil;

import com.erp.app.common.ProjectConstants;
import com.erp.app.common.util.ProjectCommonUtil;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.types.CustomSqlDate;
import com.erp.app.setup.country.criteria.CountryHdrSrchCriteria;

/**
* <p>A <strong>CommonDao</strong> provides database access functions
*  for delegate classes.
*
* @author          Craig Chun
* @version 1.0     2007/07/12
* @since           FrameWork 1.1
*
*/
public class CountryCommonDao {

	private CountryCommonUtil util = new CountryCommonUtil();

	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Craig Chun
	* @since      Framework 1.1
	*/
	public static String getHdrSqlQuery(SimpleBaseSrchCriteria obj) {

		CountryHdrSrchCriteria criteria = (CountryHdrSrchCriteria) obj;
		String sql = "";
		String whereClause = hdrToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "ORDER BY COMPANY_CODE, COUNTRY_CODE";

		if(!criteria.isSearchResult()){
			sql = "SELECT COMPANY_CODE, COUNTRY_CODE, COUNTRY_NAME, ISO_COUNTRY_CODE, COUNTRY_ABBR, "
			+ "STATUS, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE "
			+ "FROM T_COUNTRY_HDR "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		} else {
			sql = "SELECT COMPANY_CODE, COUNTRY_CODE, COUNTRY_NAME, ISO_COUNTRY_CODE, COUNTRY_ABBR, "
				+ "STATUS, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE "
				+ "FROM T_COUNTRY_HDR "
				+ whereClause
				+ groupByClause
				+ orderByClause;
		}

		return sql;
	}

	/**
	* Sets the values of a result set to a vo for a search screen
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Craig Chun
	* @since      Framework 1.1
	*/
	public static SimpleBaseVo getHdrSearchResultSetValues(ResultSet rs) throws SQLException {

		 CountryHdrVo vo = new CountryHdrVo();

	        vo.setCompanyCode(rs.getString("COMPANY_CODE"));
	        vo.setCountryCode(rs.getString("COUNTRY_CODE"));
	        vo.setHdrStatus(rs.getString("STATUS"));
	        vo.setHdrCountryName(rs.getString("COUNTRY_NAME"));
	        vo.setHdrIsoCountryCode(rs.getString("ISO_COUNTRY_CODE"));
	        vo.setHdrCountryAbbr(rs.getString("COUNTRY_ABBR"));
	        vo.setCreateUser(rs.getString("CREATE_USER"));
	        vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
	        vo.setUpdateUser(rs.getString("UPDATE_USER"));
	        vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));

	        return vo;

	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Craig Chun
	* @since      Framework 1.1
	*/
	public static String hdrToWhereClause(CountryHdrSrchCriteria crit) {

		CountryHdrSrchCriteria criteria = (CountryHdrSrchCriteria) crit;
		StringBuffer whereClause = new StringBuffer();


		ProjectCommonUtil.addHdrEquals(whereClause,  "COMPANY_CODE",            criteria.getSrchCompanyCode());
		ProjectCommonUtil.addHdrBetween(whereClause, "COUNTRY_CODE",            criteria.getSrchCountryCodeFr(), criteria.getSrchCountryCodeTo());
		ProjectCommonUtil.addHdrEquals(whereClause,  "upper(ISO_COUNTRY_CODE)", criteria.getSrchHdrIsoCountryCodeFr());
        ProjectCommonUtil.addHdrBetween(whereClause, "upper(COUNTRY_ABBR)",     criteria.getSrchHdrCountryAbbrFr(), criteria.getSrchHdrCountryAbbrTo());
		ProjectCommonUtil.addHdrEquals(whereClause,  "STATUS",                  criteria.getSrchHdrStatus());
		ProjectCommonUtil.addHdrLike(whereClause,    "COUNTRY_NAME",            criteria.getSrchHdrCountryNameFr());

		return whereClause.toString();
	}

}
